package com.idas.comp;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;

import com.idas.comp.enums.ConditionStatus;
import com.idas.comp.enums.ResourceType;
import com.idas.comp.enums.Status;
import com.idas.db.DBFields;
import com.idas.db.DBFilter;
import com.idas.comp.Address;
import com.idas.common.Util;
import com.idas.wsdl.XmlDocumentation;

public class User extends Resource {

	@DBFields(field = "firstname", table = "user")
	@XmlDocumentation(value = "The first name of the user")
	private String firstName;
	@DBFields(field = "knownname", table = "user")
	@XmlDocumentation(value = "The middle/known name of the user")
	private String knownName;
	@DBFields(field = "lastname", table = "user")
	@XmlDocumentation(value = "The lastname name of the user")
	private String lastName;
	@XmlDocumentation(value = "The address details associated with the user")
	private Address address = new Address();
	@XmlDocumentation(value = "The email address associated with the user")
	@DBFields(field = "email", table = "user")
	private String email;
	@XmlDocumentation(value = "The telephone number associated with the user")
	@DBFields(field = "telephone", table = "user")
	private String telephoneNumber;
	@XmlDocumentation(value = "A flag to indicate whether the user is active")
	@DBFields(field = "usertype_id", table = "user")
	private Status status;
	@XmlDocumentation(value = "A flag to indicate whether the user has signed the required oath")
	private ConditionStatus signedOath;
	@XmlDocumentation(value = "A flag to indicate whether the user may work in the UK")
	private ConditionStatus canUKWork;

	static {
		DBFilter.registerClass(User.class);
	}

	public User() {
	}

	protected User(ResultSet rs) throws SQLException {
		super(rs);
		firstName = rs.getString("firstname");
		knownName = rs.getString("knownname");
		lastName = rs.getString("lastname");
		Address addr = new Address(rs);
		address = addr;
		email = rs.getString("email");
		telephoneNumber = rs.getString("telephone");
		status = Status.getValue(rs.getInt("usertype_id"));
		signedOath = ConditionStatus.getValue(rs.getInt("signed_oath"));
		canUKWork = ConditionStatus.getValue(rs.getInt("ukwork"));
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public void setFirstName(Connection conn, String firstName)
			throws SQLException {
		String sql = "UPDATE user SET firstname = ? WHERE uuid = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, firstName);
		ps.setString(2, super.getUuid());
		ps.execute();
		ps.close();
		setFirstName(firstName);
	}

	public String getKnownName() {
		return knownName;
	}

	public void setKnownName(String knownName) {
		this.knownName = knownName;
	}

	public void setKnownName(Connection conn, String knownName)
			throws SQLException {
		String sql = "UPDATE user SET knownname = ? WHERE uuid = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, knownName);
		ps.setString(2, super.getUuid());
		ps.execute();
		ps.close();
		setKnownName(knownName);
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public void setLastName(Connection conn, String lastName)
			throws SQLException {
		String sql = "UPDATE user SET lastname = ? WHERE uuid = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, lastName);
		ps.setString(2, super.getUuid());
		ps.execute();
		ps.close();
		setLastName(lastName);
	}

	public Address getAddress() {
		return address;
	}

	public void setAddress(Address address) {
		this.address = address;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public void setEmail(Connection conn, String email) throws SQLException {
		String sql = "UPDATE user SET email = ? WHERE uuid = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, email);
		ps.setString(2, super.getUuid());
		ps.execute();
		ps.close();
		setEmail(email);
	}

	public String getTelephoneNumber() {
		return telephoneNumber;
	}

	public void setTelephoneNumber(String telephoneNumber) {
		this.telephoneNumber = telephoneNumber;
	}

	public void setTelephoneNumber(Connection conn, String telephoneNumber)
			throws SQLException {
		String sql = "UPDATE user SET telephone = ? WHERE uuid = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, telephoneNumber);
		ps.setString(2, super.getUuid());
		ps.execute();
		ps.close();
		setTelephoneNumber(telephoneNumber);
	}

	public Status getStatus() {
		return status;
	}

	public void setStatus(Status status) {
		this.status = status;
	}

	public void setStatus(Connection conn, Status status) throws SQLException {
		String sql = "UPDATE user SET usertype_id = ? WHERE uuid = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, Status.getValue(status));
		ps.setString(2, super.getUuid());
		ps.execute();
		ps.close();
		setStatus(status);
	}

	public ConditionStatus getSignedOath() {
		return signedOath;
	}

	public void setSignedOath(ConditionStatus signedOath) {
		this.signedOath = signedOath;
	}

	public void setSignedOath(Connection conn, ConditionStatus signedOath)
			throws SQLException {
		String sql = "UPDATE user SET signed_oath = ? WHERE uuid = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, ConditionStatus.getValue(signedOath));
		ps.setString(2, super.getUuid());
		ps.execute();
		ps.close();
		setSignedOath(signedOath);
	}

	public ConditionStatus getCanUKWork() {
		return canUKWork;
	}

	public void setCanUKWork(ConditionStatus canUKWork) {
		this.canUKWork = canUKWork;
	}

	public void setCanUKWork(Connection conn, ConditionStatus canUKWork)
			throws SQLException {
		String sql = "UPDATE user SET ukwork = ? WHERE uuid = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, ConditionStatus.getValue(canUKWork));
		ps.setString(2, super.getUuid());
		ps.execute();
		ps.close();
		setCanUKWork(canUKWork);
	}

	public void modifyUser(Connection conn, User updatedUser) throws Exception {
		Address currAddress = getAddress();
		Address updatedAddress = updatedUser.getAddress();

		if (Util.requiresUpdate(getName(), updatedUser.getName())) {
			setName(conn, updatedUser.getName());
		}

		if (!getFirstName().equals(updatedUser.getFirstName())) {
			setFirstName(conn, updatedUser.getFirstName());
		}

		if (!getKnownName().equals(updatedUser.getKnownName())) {
			setKnownName(conn, updatedUser.getKnownName());
		}

		if (!getLastName().equals(updatedUser.getLastName())) {
			setLastName(conn, getLastName());
		}

		currAddress.modifyAddress(conn, updatedAddress, getUuid());

		if (Util.requiresUpdate(getEmail(), updatedUser.getEmail())) {
			setEmail(conn, updatedUser.getEmail());
		}

		if (Util.requiresUpdate(getTelephoneNumber(),
				updatedUser.getTelephoneNumber())) {
			setTelephoneNumber(conn, updatedUser.getTelephoneNumber());
		}

	}

	public void setPassword(Connection conn, String password)
			throws SQLException {
		String sql = "UPDATE user SET password = PASSWORD(?) WHERE uuid = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, password);
		ps.setString(2, getUuid());
		ps.execute();
		ps.close();
	}

	public static ArrayList<User> getUserListForUUID(Connection conn,
			String uuid, UserAccess uac) throws Exception {
		ArrayList<User> list = new ArrayList<User>();

		Resource res = Resource.getResourceForUUID(conn, uuid);

		if (null == res) {
			throw new Exception("Unable to find system resource");
		}

		Status currUserStatus = uac.getUser().getStatus();

		String sql = "";
		PreparedStatement ps;
		ResultSet rs;

		switch (res.getType()) {
		case USER:

			switch (currUserStatus) {
			case STUDENT:
				if (uac.getUser().getUuid().equalsIgnoreCase(uuid)) {
					list.add(uac.getUser());
				} else {
					throw new Exception("Unable able to search for this UUID");
				}
				break;
			case STAFF:
				sql = "SELECT su.*, u.* FROM system_uuid su, user u, user_module um, module m WHERE "
						+ "su.system_uuid = ? AND  su.system_uuid = u.uuid AND u.user_id = um.user_id "
						+ "um.module_id = m.module_id AND m.owner_id = ?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, uuid);
				ps.setLong(2, uac.getUserId());
				rs = ps.executeQuery();
				while (rs.next()) {
					User u = new User(rs);
					list.add(u);
				}
				rs.close();
				ps.close();
				break;
			case ADMIN:
				User u = getUserForUUID(conn, uuid);
				list.add(u);
				break;
			default:
				throw new Exception("Unable able to search for this UUID");
			}

			break;
		case MODULE:
			switch (currUserStatus) {
			case STAFF:
				sql = "SELECT su.*, u.* FROM system_uuid su, user u, user_module um, module m WHERE "
						+ "su.system_uuid = u.uuid AND u.user_id = um.user_id "
						+ "um.module_id = m.module_id m.uuid = ? AND m.owner_id = ?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, uuid);
				ps.setLong(2, uac.getUserId());
				rs = ps.executeQuery();
				while (rs.next()) {
					User u = new User(rs);
					list.add(u);
				}
				rs.close();
				ps.close();
				break;
			case ADMIN:
				sql = "SELECT su.*, u.* FROM system_uuid su, user u, user_module um, module m WHERE "
						+ "su.system_uuid = u.uuid AND u.user_id = um.user_id "
						+ "um.module_id = m.module_id m.uuid = ?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, uuid);
				rs = ps.executeQuery();
				while (rs.next()) {
					User u = new User(rs);
					list.add(u);
				}
				rs.close();
				ps.close();
				break;
			default:
				throw new Exception("Unable able to search for this UUID");
			}
			break;
		case EXPENSE:
			switch (currUserStatus) {
			case STAFF:
				sql = "SELECT su.*, u.* FROM system_uuid su, user u, expense e, module m, WHERE su.system_uuid = u.uuid "
						+ "AND u.uuid = e.user_uuid AND e.uuid = ? AND e.module_uuid = m.uuid AND m.owner_id = ?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, uuid);
				ps.setLong(2, uac.getUserId());
				rs = ps.executeQuery();
				while (rs.next()) {
					User u = new User(rs);
					list.add(u);
				}
				rs.close();
				ps.close();
				break;
			case ADMIN:
				sql = "SELECT su.*, u.* FROM system_uuid su, user u, expense e WHERE su.system_uuid = u.uuid AND u.uuid = e.user_uuid "
						+ "AND e.uuid = ?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, uuid);
				rs = ps.executeQuery();
				while (rs.next()) {
					User u = new User(rs);
					list.add(u);
				}
				rs.close();
				ps.close();
				break;
			default:
				throw new Exception("Unable able to search for this UUID");
			}
			break;
		}

		return list;
	}

	public static User getUserForUUID(Connection conn, String uuid)
			throws Exception {

		User user = null;

		String sql = "SELECT * FROM system_uuid s, user u WHERE s.system_uuid = ? AND s.system_uuid = u.uuid";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, uuid);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			user = new User(rs);
		}
		rs.close();
		ps.close();
		return user;
	}

	public static User createUser(Connection conn, User user, String password)
			throws Exception {
		User existingUser = null;

		String sql = "SELECT * FROM user WHERE email = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, user.getEmail());
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			existingUser = new User(rs);
		}

		if (null != existingUser) {
			switch (existingUser.getStatus()) {
			case CLOSED:
				throw new Exception(
						"There is a closed account with email address. Please contact an administator to re-open this account.");
			case DISABLED:
				throw new Exception(
						"There is a disabled account with email address. Please contact an administator to re-open this account.");
			default:
				throw new Exception(
						"There is already an account with email address.");
			}
		}

		rs.close();
		ps.close();

		Address addr = user.getAddress();

		if (null == addr) {
			throw new Exception("No address details given");
		}

		Country c = Country.getCountryFromCode(conn, addr.getCountry());

		if (null == c) {
			throw new Exception("This not a valid country code");
		}

		sql = "INSERT INTO user "
				+ "(firstname, knownname, lastname, address1, address2, town, county, postcode, country, telephone, email, password, active, activationkey, usertype) "
				+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,PASSWORD(?),?,?,?)";
		ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		ps.setString(1, user.getFirstName());
		ps.setString(2, user.getKnownName());
		ps.setString(3, user.getLastName());
		ps.setString(4, addr.getAddress1());
		ps.setString(5, addr.getAddress2());
		ps.setString(6, addr.getTown());
		ps.setString(7, addr.getCounty());
		ps.setString(8, user.getTelephoneNumber());
		ps.setString(8, user.getEmail());
		ps.setString(9, password);
		ps.setInt(10, Status.getValue(Status.DISABLED));

		ps.execute();
		rs = ps.getGeneratedKeys();

		long userId = -1;

		while (rs.next()) {
			userId = rs.getLong(1);
		}

		rs.close();
		ps.close();

		String uuid = Util.generateResourceUUID(ResourceType.USER, userId,
				userId);

		Resource res = Resource.addResourceToDatabase(conn, uuid,
				user.getFirstName() + " " + user.getLastName(), userId,
				ResourceType.USER);

		if (null == res) {
			throw new Exception("Fail to create user");
		}

		sql = "UPDATE user SET uuid = ? WHERE user_id = ?";
		ps = conn.prepareStatement(sql);
		ps.setString(1, uuid);
		ps.setLong(2, userId);

		ps.execute();
		ps.close();

		return getUserForUUID(conn, uuid);
	}

}
